package com.hp.cmsz.web.monitoroperation;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.xmlbeans.XmlException;
import org.apache.xmlbeans.XmlToken;
import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps;
import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D;
import org.openxmlformats.schemas.drawingml.x2006.wordprocessingDrawing.CTInline;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

import java.io.*;
import java.math.BigInteger;
import java.util.List;

/**
 * @Description:  WORD 和  XLS 文档操作类
 * @Author: m01
 * @Date: created at  2018/11/9  11:01
 * @Modified By:
 */
public class WordAndExcelUtil {


    public static void main(String[] ss) {
        testXls();

    }

    private static void testXls() {

        HSSFWorkbook hssfWorkbook = WordAndExcelUtil.createXlsFile();
        HSSFSheet sheet1 = WordAndExcelUtil.createSheet(hssfWorkbook, "sheet1");
        HSSFCellStyle cellStyle = WordAndExcelUtil.createCellStyle(hssfWorkbook);
        for (int row = 0; row < 10; row++) {

            HSSFRow hssfRow = WordAndExcelUtil.createRow(sheet1, row);
            for (int col = 0; col < 3; col++) {
                HSSFCell cell = WordAndExcelUtil.createCell(hssfRow, col);
                WordAndExcelUtil.setCellValue(cell, "zqp");
                WordAndExcelUtil.setCellStyle(cell, cellStyle);
            }

        }
        WordAndExcelUtil.saveXlsFile(hssfWorkbook, new File("e:/test/a.xls"));
    }

    private static void testWord() {
        WordDocumnet wordDocumnet = WordAndExcelUtil.createWordFile();
        WordAndExcelUtil.setParagraph(wordDocumnet, "moly", 14);
        WordAndExcelUtil.setBr(wordDocumnet);
        WordAndExcelUtil.insertAPic(wordDocumnet, new File("D:/aaa.jpg"));
        WordAndExcelUtil.setBr(wordDocumnet);
        XWPFTable table = WordAndExcelUtil.createTable(wordDocumnet, 3, 5);
        WordAndExcelUtil.setTableWidth(table, "8000");
        for (int row = 0; row < 3; row++) {
            for (int col = 0; col < 5; col++) {
                XWPFTableCell cell = table.getRow(row).getCell(col);
                WordAndExcelUtil.setCellWidth(cell, 8000 / 5);
            }

        }
        WordAndExcelUtil.mergeRows(table, 0, 0, 1);
        WordAndExcelUtil.mergeColumns(table, 0, 1, 3);
        int finalRow = table.getNumberOfRows();
        for (int row = 0; row < finalRow; row++) {
            List<XWPFTableCell> cells = table.getRow(row).getTableCells();
            for (XWPFTableCell cell : cells) {
                WordAndExcelUtil.setTableCellStyle(cell, "");
                WordAndExcelUtil.setTableCellValue(cell, "zqp");
            }
        }
        WordAndExcelUtil.saveWordFile(wordDocumnet, new File("E:\\test/a.docx"));
    }


    //创建 word文档
    public final static WordDocumnet createWordFile() {
        return new WordDocumnet();
    }

    //保存word文档
    public final static boolean saveWordFile(WordDocumnet wordDocumnet, File file) {
        boolean isOk = false;
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            wordDocumnet.write(fileOutputStream);
            fileOutputStream.close();
            isOk = true;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return isOk;

    }

    //在 word中创建表格
    public final static XWPFTable createTable(WordDocumnet document, int row, int cols) {
        XWPFTable table = document.createTable(row, cols);

        return table;

    }

    public final static void setTableWidth(XWPFTable table, String width) {
        CTTbl ttbl = table.getCTTbl();
        CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl.getTblPr();
        CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr.addNewTblW();
        CTJc cTJc = tblPr.addNewJc();
        cTJc.setVal(STJc.Enum.forString("center"));
        tblWidth.setW(new BigInteger(width));
        tblWidth.setType(STTblWidth.DXA);
    }

    //合并行
    public final static void mergeRows(XWPFTable table, int colindex, int fromrow, int torow) {
        for (int rowindex = fromrow; rowindex <= torow; rowindex++) {
            XWPFTableCell cell = table.getRow(rowindex).getCell(colindex);

            if (rowindex == fromrow) {
                cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);
            } else {
                cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
            }
        }
    }

    //合并列
    public final static void mergeColumns(XWPFTable table, int rowindex, int fromcolumn, int tocolumn) {
        BigInteger totalWidth = new BigInteger("0");
        for (int colindex = fromcolumn; colindex <= tocolumn; colindex++) {
            XWPFTableCell cell = table.getRow(rowindex).getCell(colindex);
            BigInteger width = cell.getCTTc().getTcPr().getTcW().getW();
            totalWidth.add(width);
            if (colindex == fromcolumn) {
                cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
            } else {
                cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
            }
        }
        XWPFTableCell cell = table.getRow(rowindex).getCell(fromcolumn);
        cell.getCTTc().getTcPr().getTcW().setW(totalWidth);

    }

    //填充word中表格的单元格内容
    public final static void setTableCellValue(XWPFTableCell cell, String value) {

        cell.setText(value);

    }

    public final static void setTableCellStyle(XWPFTableCell cell, String value) {
        CTTc cttc = cell.getCTTc();
        CTTcPr ctPr = cttc.addNewTcPr();
        ctPr.addNewVAlign().setVal(STVerticalJc.CENTER);
        cttc.getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);
    }

    //设置单元格宽度
    public final static void setCellWidth(XWPFTableCell cell, int width) {
        CTTc cttc = cell.getCTTc();
        CTTcPr ctPr = cttc.addNewTcPr();
        ctPr.addNewTcW().setW(BigInteger.valueOf(width));
    }

    //换行
    public final static void setBr(WordDocumnet document) {
        //换行
        XWPFParagraph paragraph1 = document.createParagraph();
        XWPFRun paragraphRun1 = paragraph1.createRun();
        paragraphRun1.setText("\r");
    }

    //写段落
    public final static void setParagraph(WordDocumnet document, String value, int fontSize) {

        //添加标题
        XWPFParagraph titleParagraph = document.createParagraph();
        //设置段落居中
        titleParagraph.setAlignment(ParagraphAlignment.LEFT);

        XWPFRun titleParagraphRun = titleParagraph.createRun();
        titleParagraphRun.setText(value);
        titleParagraphRun.setColor("000000");
        titleParagraphRun.setFontSize(fontSize);

    }

    //插入图片
    public final static void insertAPic(WordDocumnet document, File file) {

        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            byte[] arr = new byte[1024];
            int len = -1;
            while ((len = fileInputStream.read(arr)) != -1) {
                byteArrayOutputStream.write(arr, 0, len);
            }
            ByteArrayInputStream in = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());

            String ind = document.addPictureData(in, XWPFDocument.PICTURE_TYPE_PNG);
            XWPFParagraph xwpfParagraph = document.createParagraph();
            xwpfParagraph.setAlignment(ParagraphAlignment.CENTER);
            document.createPicture(xwpfParagraph, document.getAllPictures().size() - 1, 400, 200, "    ");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //------------------------  xls文档操作  ----------------------------
    public final static HSSFWorkbook createXlsFile() {

        return new HSSFWorkbook();
    }

    //创建新的sheet
    public final static HSSFSheet createSheet(HSSFWorkbook hssfWorkbook, String sheetName) {
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(sheetName);
        return hssfSheet;
    }

    //在sheet中创建 行
    public final static HSSFRow createRow(HSSFSheet hssfSheet, int index) {
        HSSFRow row = hssfSheet.createRow(index);
        return row;

    }

    //在行中创建 数据格
    public final static HSSFCell createCell(HSSFRow row, int index) {
        return row.createCell(index);
    }

    //设置数据格的样式
    public final static void setCellStyle(HSSFCell cell, CellStyle style) {
        cell.setCellStyle(style);
    }

    //设置数据格内容
    public final static void setCellValue(HSSFCell cell, String value) {
        cell.setCellValue(value);
    }

    //创建样式
    public final static HSSFCellStyle createCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        //设置字体
        Font headFont = workbook.createFont();
        headFont.setFontName("黑体");
        headFont.setFontHeightInPoints((short) 9);
        headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(headFont);
        return cellStyle;
    }


    public final static void saveXlsFile(HSSFWorkbook hssfWorkbook, File file) {
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            hssfWorkbook.write(fileOutputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


}

class WordDocumnet extends XWPFDocument {

    public void test() {
        String outputFile = "D:\\test.docx";
        CustomXWPFDocument document = new CustomXWPFDocument();
        FileOutputStream fOut;
        try {
            fOut = new FileOutputStream(outputFile);
            ByteArrayInputStream in = getPieChartImage();
            String ind = document.addPictureData(in, XWPFDocument.PICTURE_TYPE_PNG);
            System.out.println("pic ID=" + ind);
            document.createPicture(document.createParagraph(), document.getAllPictures().size() - 1, 200, 200, "    ");
            document.write(fOut);
            fOut.flush();
            // 操作结束，关闭文件
            fOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static ByteArrayInputStream getPieChartImage() {
        ByteArrayInputStream in = null;

        try {

            File file = new File("e:/a.png");
            FileInputStream fileInputStream = new FileInputStream(file);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            byte[] arr = new byte[1024];
            int len = -1;
            while ((len = fileInputStream.read(arr)) != -1) {
                out.write(arr, 0, len);
            }
            in = new ByteArrayInputStream(out.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return in;
    }


    public WordDocumnet(InputStream in) throws IOException {
        super(in);
    }

    public WordDocumnet() {
        super();
    }

    public WordDocumnet(OPCPackage pkg) throws IOException {
        super(pkg);
    }

    public void createPicture(XWPFParagraph paragraph, int id, int width, int height, String picAttch) {
        final int EMU = 9525;
        width *= EMU;
        height *= EMU;
        String blipId = getAllPictures().get(id).getPackageRelationship()
                .getId();

        CTInline inline = paragraph.createRun().getCTR()
                .addNewDrawing().addNewInline();
        paragraph.createRun().setText(picAttch);
        String picXml = ""
                + "<a:graphic xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\">"
                + "   <a:graphicData uri=\"http://schemas.openxmlformats.org/drawingml/2006/picture\">"
                + "      <pic:pic xmlns:pic=\"http://schemas.openxmlformats.org/drawingml/2006/picture\">"
                + "         <pic:nvPicPr>" + "            <pic:cNvPr id=\""
                + id
                + "\" name=\"Generated\"/>"
                + "            <pic:cNvPicPr/>"
                + "         </pic:nvPicPr>"
                + "         <pic:blipFill>"
                + "            <a:blip r:embed=\""
                + blipId
                + "\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"/>"
                + "            <a:stretch>"
                + "               <a:fillRect/>"
                + "            </a:stretch>"
                + "         </pic:blipFill>"
                + "         <pic:spPr>"
                + "            <a:xfrm>"
                + "               <a:off x=\"0\" y=\"0\"/>"
                + "               <a:ext cx=\""
                + width
                + "\" cy=\""
                + height
                + "\"/>"
                + "            </a:xfrm>"
                + "            <a:prstGeom prst=\"rect\">"
                + "               <a:avLst/>"
                + "            </a:prstGeom>"
                + "         </pic:spPr>"
                + "      </pic:pic>"
                + "   </a:graphicData>" + "</a:graphic>";

        // CTGraphicalObjectData graphicData =
        inline.addNewGraphic().addNewGraphicData();
        XmlToken xmlToken = null;
        try {
            xmlToken = XmlToken.Factory.parse(picXml);
        } catch (XmlException xe) {
            xe.printStackTrace();
        }
        inline.set(xmlToken);
        // graphicData.set(xmlToken);

        inline.setDistT(0);
        inline.setDistB(0);
        inline.setDistL(0);
        inline.setDistR(0);

        CTPositiveSize2D extent = inline.addNewExtent();
        extent.setCx(width);
        extent.setCy(height);

        CTNonVisualDrawingProps docPr = inline.addNewDocPr();
        docPr.setId(id);
        docPr.setName("图片" + id);
        docPr.setDescr("");
    }
}

